﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public class EngagedInIndustryService
    {
       
        public bool SearchTable()
        {
            SqlConnection conn = ConnPoll.GetConn();
            bool tempbool = false;
            string sql = "select * from EngagedInIndustry";
            ConnPoll.Open();
            SqlCommand com = new SqlCommand(sql, conn);
            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                tempbool = true;
                break;
            }
            dr.Close();
            com = null;
            ConnPoll.Close();
            return tempbool;
        }

        #region 创建从事行业表
        /// <summary>
        /// 创建从事行业表
        /// </summary>
        public void CreateEngagedInIndustry()
        {
            SqlConnection conn = ConnPoll.GetConn();
            try
            {
                ConnPoll.Open();
                string tbna = " EngagedInIndustry";
                string sql = " use bds249611391_db"
                    + " create table " + tbna
                    + " ("
                    + " EngagedInIndustryId int not null identity(1,1)," // 主键Id
                    + " Explain nvarchar(100) null,"
                    + " EngagedInIndustryName nvarchar(100) not null"//行业名称
                    + " )"
                    + " use bds249611391_db"
                    + " alter table " + tbna
                    + " add constraint PK_EngagedInIndustryId primary key (EngagedInIndustryId)";
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                com = null;
                ConnPoll.Close();
                Insert(new EngagedInIndustry() { Explain = "从事行业", EngagedInIndustryName = string.Empty });
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 新增信息
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="eii"></param>
        /// <returns></returns>
        public int Insert(EngagedInIndustry eii)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "insert into EngagedInIndustry (Explain,EngagedInIndustryName) values (@Explain,@EngagedInIndustryName)";
            if (eii.Explain == string.Empty)
                sql= "insert into EngagedInIndustry (Explain,EngagedInIndustryName) values (default,@EngagedInIndustryName)";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@Explain",eii.Explain),
                new SqlParameter("@EngagedInIndustryName",eii.EngagedInIndustryName)
            };
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 删除信息
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="eii"></param>
        /// <returns></returns>
        public int Delete(EngagedInIndustry eii)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "delete EngagedInIndustry where EngagedInIndustryName=@EngagedInIndustryName";
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql,new SqlParameter("@EngagedInIndustryName", eii.EngagedInIndustryName));
        }
        #endregion

        #region 修改信息
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="eii"></param>
        /// <returns></returns>
        public int Set(EngagedInIndustry eii,string setStr)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "update EngagedInIndustry"
                + " set EngagedInIndustryName=@setStr"
                + " where EngagedInIndustryName=@EngagedInIndustryName";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@setStr",setStr),
                new SqlParameter("@EngagedInIndustryName",eii.EngagedInIndustryName)
            };
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 查询信息
        /// <summary>
        /// 查询信息
        /// </summary>
        /// <param name="eii"></param>
        /// <returns></returns>
        public List<EngagedInIndustry> SearchAll()
        {
            SqlConnection conn = ConnPoll.GetConn();
            List<EngagedInIndustry> leii = new List<EngagedInIndustry>();
            string sql = "select * from EngagedInIndustry";
            SqlDataReader dr = SQLHelper.ExecuteReader(conn,System.Data.CommandType.Text,sql);
            while (dr.Read())
            {
                if (dr["EngagedInIndustryName"].ToString() == string.Empty)
                    continue;
                EngagedInIndustry e = new EngagedInIndustry();
                e.EngagedInIndustryId = Convert.ToInt32(dr["EngagedInIndustryId"]);
                e.EngagedInIndustryName = dr["EngagedInIndustryName"].ToString();
                leii.Add(e);
            }
            dr.Close();
            return leii;
        }
        #endregion

        #region 查询标题
        /// <summary>
        /// 查询标题
        /// </summary>
        /// <returns></returns>
        public string SearchTitle()
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select Explain from EngagedInIndustry";
            return SQLHelper.ExecuteScalar(conn,System.Data.CommandType.Text,sql).ToString();
        }
        #endregion

        #region 查找库中是否已有此内容
        /// <summary>
        /// 查找库中是否已有此内容
        /// </summary>
        /// <param name = "eii" ></ param >
        /// < param name="UserName"></param>
        /// <returns></returns>
        public int Search(EngagedInIndustry eii, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select count(1) from EngagedInIndustry" + UserName + " where EngagedInIndustryName=@EngagedInIndustryName";
            return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql, new SqlParameter("@EngagedInIndustryName", eii.EngagedInIndustryName)));
        }
        #endregion


    }
}
